/* ============================================================================= */
/*                   TABLE 7 - FINTECH VS BANK BORROWERS   */
/* ============================================================================= */

clear all

/*****************
 Data Preparation
******************/

/* Import department mapping for CIB-guichet */
import delimited "$input\CIB_guichet_departement.csv", clear
save "$output\CIB_guichet_departement.dta", replace	

/* Import SIREN and weights from PSM */
use "$output\matched_sample_nn.dta", clear
    keep siren weight  // Keep only firm identifier and weight
    sort siren
    quietly by siren: gen dup = cond(_N == 1, 0, _n)  // Identify duplicates
    drop if dup > 1  // Remove duplicates
    save "$output\matched_weights.dta", replace  // Save weights file

/***************************************
*           UNMATCHED SAMPLE           *
***************************************/

/* Import SIREN - CIB dataset */
use "$output\SCR_CIB_3_months_orbis_scrapped.dta", clear

/* Keep only relevant dates */
keep if mdate < mofd(date("20200101","YMD"))
keep if mdate > mofd(date("20131231","YMD"))

/***************************************
*             MATCHED SAMPLE           *
***************************************/

/* Merge with department dataset to get lender location */
merge m:1 cib guichet using "$output\CIB_guichet_departement.dta"
keep if _merge == 3  // Keep only matched records
drop _merge

/* Merge Corsica into a single department */
replace dept = "2" if dept == "2A" | dept == "2B"
destring dept, gen(deptfirm) force  // Convert department codes to numeric

/* Identify whether lender is local or distant */
gen near = deptfirm == departement  // 1 if lender is in the same department

/* Merge with weights and keep only matched SIRENs */
merge m:1 siren using "$output\matched_weights.dta"
keep if _merge == 3
drop _merge

/* Start logging */
capture log close
log using "$logs\Table_7_IFP.log", replace

/* Identify new banking relationships */
gen newbankrelation = recentbankmedian

/* Filter data based on relevant conditions */
keep if newoutside == 0
keep if inrange(deltamonths, -12, 24)  // Keep only observations within -12 to +24 months
sort siren mdate cib guichet

/****************************
 Distant vs. Near Lenders
****************************/
preserve
    gen loan = available + drawn  // Total loan amount

    /* Split loan by lender proximity */
    gen loannear = loan * (near == 1)  // Loan from local lenders
    gen loanfar = loan * (near == 0)  // Loan from distant lenders

    /* Aggregate loan amounts */
    collapse (sum) loannear loanfar loan, by(siren p2p post mdate deltamonths year rating contrancollateral forinvestment weight earlyrepayment)

    /* Log transformation */
    gen lloannear = log(loannear)
    gen lloanfar = log(loanfar)

    /* Run regressions */
    capture estimates drop *
    eststo regnear: reghdfe lloannear i1.p2p##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren) 
	estadd local yearfirm "Y", replace   
    estadd local mdateFE "Y", replace 
	
    eststo regfar: reghdfe lloanfar i1.p2p##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren) 
    estadd local yearfirm "Y", replace   
    estadd local mdateFE "Y", replace   
restore

/****************************
 New vs. Existing Lenders
****************************/

/* Count unique banks per firm */
egen nb_banks = tag(siren mdate cib)
egen lbank = total(nb_banks), by(siren mdate)

/* Count new and old banks separately */
egen nb_banks_old = tag(siren mdate cib) if newbankrelation == 0 
egen lbank_old = total(nb_banks_old), by(siren mdate)
egen nb_banks_new = tag(siren mdate cib) if newbankrelation == 1
egen lbank_new = total(nb_banks_new), by(siren mdate)

/* Define loan types */
gen leasing = bm + bi

/* Classify loans by lender type (new vs. existing) */
foreach var in leasing available drawn ltloan stloan co bm bi {
    gen `var'_new = `var' * (newbankrelation == 1)
    gen `var'_old = `var' * (newbankrelation == 0)
}

/* Aggregate loan amounts */
collapse (sum) stloan co co_new co_old ltloan bm bi oc available drawn leasing leasing_old leasing_new bm_new bm_old bi_new bi_old ///
    available_new drawn_new available_old drawn_old ltloan_old ltloan_new stloan_old stloan_new, ///
    by(siren p2p post mdate deltamonths year lbank lbank_old lbank_new rating contrancollateral forinvestment weight earlyrepayment)

/* Log transformation */
gen lloan = log(1 + stloan + ltloan + bm + bi + oc)
gen lloannew = log(1 + stloan_new + ltloan_new + bm_new + bi_new + available_new)
gen lloanold = log(1 + stloan_old + ltloan_old + bm_old + bi_old + available_old)

/* Run regressions */
qui eststo regnew: reghdfe lloannew i1.p2p##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren) 
estadd local yearfirm "Y", replace   
estadd local mdateFE "Y", replace 

qui eststo regexisting: reghdfe lloanold i1.p2p##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)  
estadd local yearfirm "Y", replace   
estadd local mdateFE "Y", replace  

/****************************
 Rated vs. Unrated Firms
****************************/

/* Load dataset of rated firms */
use "$output\matched_sample_nn_rating_234.dta", clear
    keep if inrange(mdate, mofd(date("20140101","YMD")), mofd(date("20191231","YMD")))
    keep if inrange(deltamonths, -12, 24)
    gen loan = stloan + ltloan + bm + bi + oc
    gen lloan = log(loan)

    /* Run regression for rated firms */
    eststo regrated: reghdfe lloan i1.p2p##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)
    estadd local yearfirm "Y", replace 
    estadd local mdateFE "Y", replace  

/* Load dataset of unrated firms */
use "$output\matched_sample_nn_rating_1.dta", clear
    keep if inrange(mdate, mofd(date("20140101","YMD")), mofd(date("20191231","YMD")))
    keep if inrange(deltamonths, -12, 24)
    gen loan = stloan + ltloan + bm + bi + oc
    gen lloan = log(loan)

    /* Run regression for unrated firms */
    eststo regunrated: reghdfe lloan i1.p2p##i1.post [pweight=weight], a(i.siren#i.year mdate) cluster(siren)
    estadd local yearfirm "Y", replace 
    estadd local mdateFE "Y", replace  

/****************************
 Export Table 7a
****************************/

/* Export results to LaTeX */
esttab regexisting regnew regnear regfar regrated regunrated using "$tables\Table_7_fin_bank.tex", replace ///
    noomitted nobase fragment gap booktabs label nonote noobs nolines /// 
    b(%9.3f) drop(_cons) se(%9.3f) star(* 0.10 ** 0.05 *** 0.01)  ///
    mtitles("Existing Lenders" "New Lenders" "Local Lenders" "Distant Lenders" "Rated" "Unrated") ///
    order(1.p2p#1.post 1.post) varlabel(1.post "Post" 1.p2p#1.post `"FinTech $\times$ Post"') ///
    stats(yearfirm mdateFE N r2, label(`"Firm-Year FE"' `"Month FE"' `"\midrule N"' `"R-sq"') layout("\multicolumn{1}c{@}" "\multicolumn{1}c{@}") fmt(%9.0fc %9.0fc %9.0fc %9.2f)) 

log close
